﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.OleDb;
using DFCLib;
using System.Runtime.InteropServices;
using System.IO;


namespace SyncS4MToDctm
{
    public partial class Form1 : Form
    {
        private DataSet ds;
        private IDfClientX m_DfClientX = null;

        public Form1()
        {
            InitializeComponent();
        }

        private void initializeDfc()
        {
            m_DfClientX = new DfClientX();
            if (m_DfClientX == null)
            {
                throw new Exception("Cannot obtain DfClientX");
            }
        }

        private IDfSessionManager SetupSessionManager(string user, string pwd, string docBase)
        {
            IDfSessionManager smgr = null;
            IDfClient client = null;
            IDfLoginInfo loginInfo = null;

            try
            {
                client = m_DfClientX.getLocalClient();

                if (client != null)
                {
                    smgr = client.newSessionManager();
                    if (smgr != null)
                    {
                        loginInfo = m_DfClientX.getLoginInfo();
                        if (loginInfo != null)
                        {
                            loginInfo.setUser(user);
                            loginInfo.setPassword(pwd);
                            smgr.setIdentity(docBase, loginInfo);
                            return smgr;
                        }
                        else
                        {
                            throw new Exception("Cannot obtain login info");
                        }
                    }
                    else
                    {
                        throw new Exception("Cannot obtain session manager");
                    }
                }
                else
                {
                    throw new Exception("Cannot obtain client");
                }
            }
            catch (Exception er)
            {
                throw er;
            }
            finally
            {
                if (client != null)
                {
                    DestroyComObject(client);
                }
                if (loginInfo != null)
                {
                    DestroyComObject(loginInfo);
                }
            }
        }

        private IDfSession SetupSession(IDfSessionManager smgr, String docBase)
        {
            if (smgr == null)
            {
                throw new Exception("Session manager is null");
            }

            IDfSession session = smgr.newSession(docBase);

            if (session == null)
            {
                throw new Exception("Cannot obtain session");
            }
            return session;
        }

        private void CleanupSession(IDfSessionManager smgr, ref IDfSession session)
        {
            try
            {
                if (smgr != null)
                {
                    if (session != null)
                    {
                        smgr.release(session);
                    }
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.ToString());
            }
            finally
            {
                if (smgr != null)
                {
                    DestroyComObject(smgr);
                }
                if (session != null)
                {
                    DestroyComObject(session);
                }
            }
        }



        private void updateDocumentum(object sender, EventArgs e)
        {
            MessageBox.Show("Beginning update of repository...", "Excel to Documentum");

            DataTable dt = ds.Tables[0];
            FileStream fs = new FileStream("dql_statements.txt", FileMode.Create);
            StreamWriter sw = new StreamWriter(fs);

            IDfSessionManager smgr = null;
            IDfSession session = null;
            IDfQuery query = null;

            try
            {
                initializeDfc();
                query = m_DfClientX.getQuery();
                smgr = SetupSessionManager("damadmin", "C@nwest", "DMS");
                session = SetupSession(smgr, "DMS");

                foreach (DataRow row in dt.Rows)
                {
                    String episodeTitle = row.Field<String>("S4M EPISODE TITLE");
                    String episodeNumber = row["DOCUMENTUM EPISODE NUMBER"].ToString();
                    String oldTitle = row.Field<String>("DOCUMENTUM EPISODE TITLE");

                    if (String.IsNullOrEmpty(episodeNumber))
                    {
                        continue;
                    }
                    
                    if (String.IsNullOrEmpty(episodeTitle))
                    {
                        continue;
                    }

                    String getId = "SELECT r_object_id FROM vf_title WHERE v_episode_number = '[episodeNumber]' AND v_episode_title = '[old title]' AND FOLDER('/Longform Video Content/By Title/M/Mad About You', DESCEND)";
                    getId = getId.Replace("[episodeTitle]", episodeTitle.Replace("'", "''"));
                    getId = getId.Replace("[old title]", oldTitle.Replace("'", "''"));
                    getId = getId.Replace("[episodeNumber]", episodeNumber);
                    query.setDQL(getId);
                    IDfCollection coll = query.execute(session, (int)tagDfQueryTypes.DF_READ_QUERY);
                    String objectId = null;

                    if (null != coll)
                    {
                        coll.next();
                        IDfTypedObject queryResultObject = coll.getTypedObject();
                        //objectId = queryResultObject.getObjectId().toString();
                        objectId = queryResultObject.getString("r_object_id");

                        coll.close();
                    }

                    String dql = "UPDATE vf_title OBJECT SET v_episode_title = '[episodeTitle]', SET object_name = '[episodeNumber] - [episodeTitle]', SET v_full_title = '[episodeTitle]', SET v_slate_comments = 'Mad About You #[episodeNumber] \"[episodeTitle]\"' WHERE r_object_id = '" + objectId + "'";
                    dql = dql.Replace("[episodeTitle]", episodeTitle.Replace("'", "''"));
                    dql = dql.Replace("[episodeNumber]", episodeNumber);
                    dql = dql.Replace("[old title]", oldTitle.Replace("'", "''"));
                    query.setDQL(dql);
                    sw.WriteLine(dql);
                    coll = query.execute(session, (int)tagDfQueryTypes.DF_EXEC_QUERY);

                    if (null != coll)
                    {
                        coll.close();
                    }

                }



                sw.Flush();
                sw.Close();
                fs.Close();
                MessageBox.Show("Done update of repository.", "Excel to Documentum");


            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString(), "Error!");
            }



        }

        private void LoadExcel(String excelName)
        {
            string connString = @"Provider=Microsoft.Jet.OleDb.4.0;"
+ "data source=" + excelName + ";"
+ "Extended Properties=Excel 8.0";

            string sql = String.Empty;
            OleDbDataAdapter adapter;

            ds = new DataSet();

            sql = "SELECT [DOCUMENTUM EPISODE NUMBER], [DOCUMENTUM EPISODE TITLE], [S4M EPISODE TITLE] FROM [Sheet1$]";
            adapter = new OleDbDataAdapter(sql, connString);
            adapter.Fill(ds);

        }

        private void openToolStripMenuItem_Click(object sender, EventArgs e)
        {
            openFileDialog1.FileOk += new CancelEventHandler(openFileDialog1_FileOk);
            openFileDialog1.ShowDialog();
        }

        void openFileDialog1_FileOk(object sender, CancelEventArgs e)
        {
            String fullExcelPath = openFileDialog1.FileName;
            LoadExcel(fullExcelPath);
        }

        private void DestroyComObject(Object obj)
        {
            Marshal.ReleaseComObject(obj);
            obj = null;
        }

        protected override void OnClosed(EventArgs e)
        {
            base.OnClosed(e);
            if (m_DfClientX != null)
            {
                DestroyComObject(m_DfClientX);
            }

        }


    }
}
